﻿using BlazorAdmin.Generator.Models;
using System.Data.Common;
using System.Collections.Generic;
using System;

namespace BlazorAdmin.Generator.DbColumnReaders
{
    public class PostgreColumnReader : DbColumnReader
    {
        public override List<Column> ReadColumns(DbConnection connection, DbProviderFactory factory, string table)
        {
            var cols = new List<Column>();

            var strSql = @$"Select Cols.Column_Name As Code, Col_Description(Clas.OID, Attr.AttNum) As Name, Cols.Udt_Name As DbType,
	                        Coalesce(Character_Maximum_Length, Numeric_Precision, DateTime_Precision, 0) As Length, Coalesce(Cols.Numeric_Scale, 0) As Point, 
	                        (Case When Cols.Is_NullAble = 'YES' Then True Else False End) As IsNull,
	                        (Case When(Select Attr.AttNum = Any(ConKey) From Pg_Constraint Where ConrelID = Clas.OID And ConType = 'p') = 't' Then True Else False End) As IsKey
                            FROM
	                            Information_Schema.Columns As Cols,
	                            Pg_Class Clas,
	                            Pg_Attribute Attr 
                            Where
	                            Cols.Table_Name = '{table}'
	                            And Clas.RelName = Cols.Table_Name
	                            And Attr.AttName = Cols.Column_Name 
	                            And Clas.OID = Attr.AttrelID 
	                            And Attr.AttNum > 0;";

            using (var cmd = factory.CreateCommand())
            {
                cmd.Connection = connection;
                cmd.CommandText = strSql;
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    cols.Add(new Column()
                    {
                        PropertyCode = reader["Code"].ToString(),
                        PropertyDesc = reader["Name"].ToString(),
                        PropertyType = GetPropertyType(reader["DbType"].ToString()),
                        Length = Convert.ToInt32(reader["Length"]),
                        Point = Convert.ToInt32(reader["Point"]),
                        IsNullable = Convert.ToBoolean(reader["IsNull"]),
                        IsKey = Convert.ToBoolean(reader["IsKey"]),
                    });
                }
            }

            return SetInputTypeAuto(cols);
        }

        string GetPropertyType(string dbType)
        {
            switch (dbType.Trim())
            {
                case "int8":
                case "serial8":
                    return "long";
                case "bool":
                    return "bool";
                case "bytea":
                    return "byte[]";
                case "float8":
                    return "double";
                case "int4":
                case "serial4":
                    return "int";
                case "money":
                case "numeric":
                    return "decimal";
                case "float4":
                    return "float";
                case "int2":
                    return "short";
                case "time":
                case "timetz":
                case "timestamp":
                case "timestamptz":
                case "date":
                    return "DateTime";
                case "uuid":
                    return "Guid";
                default:
                    return "string";
            }
        }
    }
}
